﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Net.Mail;
using System.Configuration;
using System.Net;
using System.IO;
using System.Text;
using Library.Logging;
using System.Globalization;
using System.Data.SqlTypes;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace SaleManagement.ImportFile.Common
{
  public sealed class Utilities
  {
    #region Methods
    public static string SafeString(string text)
    {
      if (text == null)
      {
        return null;
      }
      if (text.IndexOf("&nbsp;") == 0)
      {
        text = text.Substring(6);
      }

      text = text.Trim();
      if (text.Length == 0)
      {
        return null;
      }
      return text;
    }

    public static void WriteLog(string description, Exception ex)
    {
      Logger.LogError(description, ex, "Utils Log", null);
    }

    public static bool ParseInt(string text, out int amount)
    {
      try
      {
        amount = 0;
        if (!string.IsNullOrEmpty(text.Trim()))
        {
          if (!int.TryParse(text, out amount))
          {
            return false;
          }
        }

        return true;
      }
      catch (Exception ex)
      {
        WriteLog("Common.Utilities::ParseInt", ex);
        throw;
      }
    }

    public static bool ParseSQLDateTime(string text, out DateTime? date)
    {
      try
      {
        date = null;
        if (!string.IsNullOrEmpty(text.Trim()))
        {
          DateTime result;
          if (DateTime.TryParse(text.Trim(), CultureInfo.GetCultureInfo("vi-VN"), DateTimeStyles.None, out result))
          {
            if (result >= (DateTime)SqlDateTime.MinValue && result <= (DateTime)SqlDateTime.MaxValue)
            {
              date = result;
            }
            else
            {
              return false;
            }
          }
          else
          {
            return false;
          }
        }

        return true;
      }
      catch (Exception ex)
      {
        WriteLog("Common.Utilities::ParseSQLDateTime", ex);
        throw;
      }
    }

    public static DataTable GetDataFromXLS(string path, string sheet, bool isHeader)
    {
      try
      {
        string strConnectionString = string.Empty;

        if (isHeader)
          strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
          "Data Source=" + path + ";" +
          "Extended Properties=" + (char)34 + "Excel 8.0;HDR=Yes;IMEX=1;" + (char)34;
        else
          strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
        "Data Source=" + path + ";" +
        "Extended Properties=" + (char)34 + "Excel 8.0;HDR=No;IMEX=1;" + (char)34;

        OleDbConnection cnCSV = new OleDbConnection(strConnectionString);
        cnCSV.Open();
        OleDbCommand cmdSelect = new OleDbCommand(@"SELECT * FROM [" + sheet + "]", cnCSV);
        OleDbDataAdapter daCSV = new OleDbDataAdapter();
        daCSV.SelectCommand = cmdSelect;
        DataTable dtCSV = new DataTable();
        daCSV.Fill(dtCSV);
        cnCSV.Close();
        daCSV = null;
        return dtCSV;
      }
      catch (Exception ex)
      {
        WriteLog("Common.Utilities::GetDataFromXLS", ex);
        throw ex;
      }
    }

    public static void BulkCopyToImport(DataTable dt, object[] mappings, string tableName)
    {
      try
      {
        string connString = ConfigurationManager.ConnectionStrings[1].ConnectionString;
        using (SqlConnection destinationConnection = new SqlConnection(connString))
        {
          // open the connection
          destinationConnection.Open();
          using (SqlBulkCopy bulkCopy =
                      new SqlBulkCopy(connString))
          {
            bulkCopy.BulkCopyTimeout = 3600000;
            bulkCopy.BatchSize = 500;

            // column mappings
            if (mappings != null)
            {
              for (int i = 0; i < mappings.Length; i++)
              {
                SqlBulkCopyColumnMapping mapping = (SqlBulkCopyColumnMapping)mappings[i];
                bulkCopy.ColumnMappings.Add(mapping);
              }
            }

            bulkCopy.DestinationTableName = tableName;
            bulkCopy.WriteToServer(dt);
          }
        }
      }
      catch (Exception ex)
      {
        WriteLog(string.Format("Utilities.CommonHelpers::BulkCopyToImport{0}", tableName), ex);
        throw;
      }
    }
    #endregion
  }
}
